<template>
  <el-card>
    <el-container>
      <el-header>
        <el-container>
          <el-aside width = "200px">
            <el-select
                @change = "openDb"
                clearable
                filterable
                id = "dbInfo"
                placeholder = "请选择数据库信息"
                v-model = "dbIndex"
            >
              <el-option
                  :key = "sitem.configId"
                  :label = "sitem.name"
                  :value = "sitem.configId"
                  v-for = "sitem in dbList"
              >
              </el-option>
            </el-select>
          </el-aside>
          <el-main>
            <el-select style = "width: 130px; margin-right: 5px" v-model = "pageSize">
              <el-option
                  :key = "sitem"
                  :label = "'每页' + sitem + '条'"
                  :value = "sitem"
                  v-for = "sitem in pageSizeLimit"
              >
              </el-option>
            </el-select>
            <el-tooltip
                class = "box-item"
                content = "快捷键Ctrl+E"
                effect = "dark"
                placement = "right-start"
            >
              <span @click = "executeSql" style = "color: green" v-if = "!!dbIndex"
              ><i class = "fa fa-play"/> 执行
              </span>
            </el-tooltip>
            <el-divider direction = "vertical" v-if = "!!dbIndex"/>
            <span @click = "executeStop" style = "color: red" v-if = "!!dbIndex"
            ><i class = "fa fa-stop-circle"/>停止</span
            >
            <el-divider direction = "vertical" v-if = "!!dbIndex"/>
            <el-tooltip
                class = "box-item"
                content = "快捷键Ctrl+Shift+F"
                effect = "dark"
                placement = "right-start"
            >
              <span @click = "dataFormat" style = "color: darkorange" v-if = "!!dbIndex"
              ><i class = "fa fa-file-code-o"/> 格式化
              </span>
            </el-tooltip>
            <el-divider direction = "vertical" v-if = "!!dbIndex"/>
            <el-popover :width = "500" placement = "right" trigger = "click">
              <template #reference>
                <span title = "使用说明"><i class = "fa fa-info-circle"></i></span>
              </template>
              <div>
                <span>使用说明</span>
                <p>
                  1,使用前需先连接数据库;如果在左侧下拉框中,无数据或者无您要连接的DB,请联系管理员配置并授权;
                </p>
                <p>
                  2,Sql查询器理论上支持数据库的所有DDL,DML,DCL等操作,但基于合规性,请在配置数据库的时候慎重赋予操作权限;
                </p>
                <p>3,建议不要执行耗时的Sql,容易被网关拦截;</p>
                <p>4,建议每条Sql写完后加上";",以方便多条sql拆分;</p>
                <p>5,默认每条Sql 一次最多返回10条数据,页面支持最大返回100条配置;</p>
                <p>6,每次最多执行的Sql数不能超过5条,超过数量取前5条;</p>
                <p>7,执行器支持选中某条Sql进行执行;</p>
                <p>8,Ctrl+E 执行,Ctrl+Shift+F 格式化,Ctrl+Enter 打开提示.</p>
              </div>
            </el-popover>
          </el-main>
        </el-container>
      </el-header>
      <el-container>
        <el-aside style = "width: 200px; overflow-y: auto" v-if = "!!dbIndex">
          <ul>
            <template v-for = "(data, index) in tableAndColumnsList">
              <el-popover :width = "540" placement = "right" trigger = "click">
                <template #reference>
                  <li>
                    <i class = "fa fa-table"></i>
                    <el-tooltip :content = "data.comment">
                      {{ data.tableName }}
                    </el-tooltip>
                  </li>
                </template>
                <table border = "1" cellpadding = "0" cellspacing = "0" class = "el-table">
                  <thead>
                  <tr>
                    <th>名称</th>
                    <th>类型</th>
                    <th>备注</th>
                  </tr>
                  </thead>
                  <tbody>
                  <tr v-for = "sdata in data.fields">
                    <td>{{ sdata.filedName }}</td>
                    <td>{{ sdata.type }}</td>
                    <td>{{ sdata.comment }}</td>
                  </tr>
                  </tbody>
                </table>
              </el-popover>
            </template>
          </ul>
        </el-aside>
        <el-container style = "overflow-y: auto">
          <el-main>
            <textarea ref = "textarea" v-model = "value"/>
          </el-main>
          <el-footer v-if = "!!queryResult">
            <el-tabs class = "demo-tabs" type = "border-card" v-model = "activeName">
              <el-tab-pane
                  :label = "'Result' + (indexa + 1)"
                  :name = "'Result' + (indexa + 1)"
                  v-for = "(item, indexa) in queryResult"
              >
                <el-table
                    :data = "item.dataList"
                    :id = "'queryResultId' + (indexa + 1)"
                    @row-dblclick = "viewDataDetail"
                    highlight-current-row = "true"
                    ref = "multipleTable"
                    stripe
                    style = "min-width: 1000px"
                >
                  <!--  <el-table-column label="显示/隐藏" :render-header="columnListFun(item.columnNames)"/>-->
                  <el-table-column
                      :formatter = "resultDataFormat"
                      :index = "index"
                      :label = "pp"
                      :prop = "pp"
                      :show-overflow-tooltip = "true"
                      min-width = "200px"
                      sortable
                      v-for = "(pp, index) in item.columnNames"
                  >
                  </el-table-column>
                </el-table>
                <el-divider/>
                <el-pagination
                    :total = "item.totalDatas"
                    @current-change = "
                    handleCurrentChange(
                      indexa,
                      item.currentSql,
                      item.currentPage,
                      item.pageSize
                    )
                  "
                    layout = "total,  prev, pager, next,jumper"
                    v-model:currentPage = "item.currentPage"
                    v-model:page-size = "item.pageSize"
                />
              </el-tab-pane>
            </el-tabs>
          </el-footer>
        </el-container>
      </el-container>
    </el-container>

    <el-drawer
        :before-close = "handleClose"
        :direction = "direction"
        title = "数据详情"
        v-model = "drawer"
    >
      <table border = "1" cellpadding = "0" cellspacing = "0" class = "el-table">
        <thead>
        <tr class = "el-table__row">
          <th>字段名</th>
          <th>值</th>
        </tr>
        </thead>
        <tbody>
        <tr class = "el-table__row" v-for = "(val, key, index) in detailData">
          <td>{{ key }}</td>
          <td>{{ val }}</td>
        </tr>
        </tbody>
      </table>
    </el-drawer>
  </el-card>
</template>

<style lang = "scss" scoped>
.el-card {
  height: 100%;
  overflow: hidden;
}

.el-container {
  width: 100%;

  .el-header {
    width: inherit;
    height: 40px;

    .el-container {
      height: 40px;

      .el-aside {
        height: inherit;
        justify-content: center;
        vertical-align: middle;
        padding: 3px;
      }

      .el-main {
        height: inherit;
        --el-main-padding: 3px;
        overflow: hidden;

        span {
          height: 30px;
          margin-left: 10px;
          text-align: left;
          text-wrap: normal;
          flex: 1;
          cursor: pointer;
          align-items: center;
          justify-items: center;
          justify-content: flex-start;
          font-size: 13px;

          i {
            padding-right: 10px;
          }
        }
      }
    }
  }

  .el-container {
    height: 100%;

    .el-aside {
      border-right: 1px solid #f1f2f3;
      height: 100%;

      ul {
        margin: 5px;

        li {
          cursor: pointer;
          margin: 3px 0px;
        }
      }
    }

    .el-container {
      height: 600px;

      .el-main {
        --el-main-padding: 0;
        height: 350px;
      }

      .el-footer {
        --el-footer-padding: 0;
        height: calc(100% - 350px);
      }
    }
  }
}

.CodeMirror {
  flex: 1;
}

::v-deep.CodeMirror-sizer {
  font-size: 13px;
}
</style>

<script>
import {format} from "sql-formatter";
import CodeMirror from "codemirror";
import "codemirror/lib/codemirror.css";
import "codemirror/addon/hint/show-hint.css";
import "codemirror/theme/idea.css";
import "codemirror/theme/darcula.css";
import "codemirror/addon/fold/foldgutter.css";
import "codemirror/addon/fold/foldgutter";

import "codemirror/addon/edit/closebrackets.js"; // 在键入时自动关闭括号和引号
import "codemirror/lib/codemirror";
import "codemirror/mode/sql/sql";
import "codemirror/addon/hint/show-hint"; // 自动提示
import "codemirror/addon/hint/sql-hint";
import {getRequest, postRequest} from "../../api/star_horse";
import {ElLoading} from "element-plus";
import {error, warning} from "../../utils/message";
import {markRaw} from "@vue/reactivity";

export default {
  name: "DbSearch",
  components: [CodeMirror],
  data() {
    return {
      editor: null,
      cacheValue: {},
      dbList: [],
      drawer: false,
      direction: "rtl", //ltr:left to right,rtl:right to left ,ttb:top to bottom,btt:bottom to top
      queryResult: [],
      detailData: {},
      pageSize: 10,
      activeName: "Result1",
      pageSizeLimit: [10, 20, 50, 100], //每条Sql允许一次查询数据量
      tableAndColumnsList: [],
      value: "",
      dbIndex: null,
      currentIndex: null,
      readOnly: true,
    };
  },
  // watch: {
  //     value: {
  //         immediate: true,
  //         handler(value) {
  //             if (this.editor) {
  //                 if (value === undefined) {
  //                     this.editor.setValue('')
  //                 } else {
  //                     this.editor.setValue(sqlFormatter.format(this.value))
  //                 }
  //                 setTimeout(() => {
  //                     this.editor.refresh()
  //                 }, 100)
  //             }
  //         }
  //     }
  //
  // },
  created() {
    const _this = this;
    _this.initDbList();
  },
  mounted() {
    const _this = this;
    CodeMirror.resolveMode("text/x-sql").keywords["left join"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["left"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["right join"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["right"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["inner join"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["inner"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["when"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["FROM_DAYS(N)"] = true;
    CodeMirror.resolveMode("text/x-sql").keywords["UPGRADE"] = true;
    let options = {
      mode: "text/x-sql",
      theme: "darcula", // 用于设置编辑器样式的主题
      lineNumbers: true, // 是否在编辑器的左侧显示行号
      line: true,
      styleActiveLine: true,
      tabSize: 4,
      foldGutter: true,
      gutters: ["CodeMirror-linenumbers", "CodeMirror-foldgutter"],
      matchBrackets: true, // 括号匹配
      smartIndent: true, // 智能缩进
      // readOnly: this.readOnly,
      autoCloseBrackets: true, // 在键入时自动关闭括号和引号
      extraKeys: {
        // 自动补全的按键事件
        "Ctrl-E": _this.executeSql,
        "Ctrl-Shift-F": _this.dataFormat,
        ".": completeAfter,
        "Ctrl-Enter": "autocomplete",
        F11: function (cm) {
          cm.setOption("fullScreen", !cm.getOption("fullScreen"));
        },
      },
      autoRefresh: true,
      hint: CodeMirror.hint.sql,
      hintOptions: {
        completeSingle: false,
        // tables: {
        //     users: ['name', 'score', 'birthDate'],
        //     countries: ['name', 'population', 'size']
        // }
        // tables: _this.hintobj  // sql类型下自定义提示选项使用tables
        // tables结构示例： {'user': ['name', 'sex', 'id'], 'student': ['b_id', 'name']}
      },
      lineWrapping: true, // 是否应滚动或换行以显示长行
    };
    _this.editor = markRaw(CodeMirror.fromTextArea(_this.$refs.textarea, options));
    _this.editor.setSize("auto", "350px");
    // _this.editor.on("dblclick", function (ins) {
    //     error(_this.editor.getSelection());
    // });
    // _this.editor.on("cursorActivity",ins => {
    //     error(_this.editor.getSelection());
    // });
    // _this.editor.on("cursorActivity", function() {
    //     _this.editor.showHint();
    // });
    _this.editor.on("keyup", (cm, event) => {
      //A-Z 65-90
      //a-z 97-122
      //  alert(event.ctrlKey);
      if (!event.ctrlKey && event.keyCode >= 65 && event.keyCode <= 90) {
        // alert(22);
        cm.showHint({
          completeSingle: false,
        });
      }
    });

    // 对应按键下调用的自动提示方法
    function completeAfter(cm, pred) {
      if (!pred || pred()) {
        setTimeout(function () {
          if (!cm.state.completionActive) {
            cm.showHint({
              completeSingle: false,
            });
          }
        }, 100);
      }
      return CodeMirror.Pass;
    }
  },
  methods: {
    resultDataFormat(row, column, cellValue, index) {
      if (!cellValue) {
        return "-";
      }
      if (column.property == "created_date" || column.property == "updated_date") {
        let date = new Date(cellValue);
        return date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + date.getDate();
      } else {
        return cellValue;
      }
    },
    columnListFun(datas) {
      return `<el-popover placement="bottom" :width="100" trigger="click"><el-checkbox v-for="data in ${datas}" :label="data"/> </el-popover>`;
    },
    handleCurrentChange(index, sql, currentPage, pageSize) {
      let _this = this;
      let reqData = {
        sqls: [sql],
        pageSize: pageSize,
        currentPage: currentPage,
        idDbinfo: _this.dbIndex,
      };
      _this.load("数据查询中...");
      postRequest("/system-config/dbsearch/dbinfoEntity/search", reqData).then((res) => {
        _this.queryResult[index] = res.data.data[0];
        _this.closeLoad();
      });
    },

    /**
     * 查看详情
     */
    viewDataDetail(row, column, event) {
      this.drawer = !this.drawer;
      this.detailData = row;
    },
    /**
     * 加载框
     */
    load(msg) {
      if (!this.loading) {
        this.loading = ElLoading.service({
          lock: true,
          target: "#queryResultId",
          text: msg || "Loading",
          background: "rgba(0, 0, 0, 0.7)",
        });
      } else {
        this.loading.open();
      }
    },
    closeLoad() {
      if (this.loading) {
        this.loading.close();
        this.loading = null;
      }
    },
    getSql: function () {
      const _this = this;
      let sData = _this.editor.getSelection();
      if (!sData) {
        sData = _this.editor.getValue();
      }
      return sData;
    },
    executeSql: function () {
      const _this = this;
      if (!_this.dbIndex) {
        warning("执行Sql前先连接数据库");
        return;
      }
      let datas = _this.getSql();
      if (!datas) {
        return;
      }
      datas = datas.split(";");
      let tempList = [];
      datas.forEach((item) => {
        let temp = item.replaceAll("\n", "");
        temp = temp.trim();
        if (temp) {
          tempList.push(temp);
        }
      });
      // datas = datas.filter(item => !!item);
      if (tempList.length > 5) {
        warning("一次最多只能执行5条Sql");
        return;
      }
      let reqData = {
        sqls: tempList,
        pageSize: _this.pageSize,
        currentPage: 1,
        idDbinfo: _this.dbIndex,
      };
      _this.load("数据查询中...");
      postRequest("/system-config/dbsearch/dbinfoEntity/search", reqData).then((res) => {
        _this.closeLoad();
        if (res.data.code === 1) {
          error(res.data.cnMessage);
        } else {
          _this.queryResult = res.data.data;
        }
      });
    },
    executeStop: function () {
    },
    dataFormat: function () {
      const _this = this;
      let sData = _this.editor.getSelection();
      let flag = true;
      if (!sData) {
        flag = !flag;
        sData = _this.editor.getValue();
      }
      let fData = format(sData);
      if (flag) {
        _this.editor.replaceSelection(fData + "\n\t");
      } else {
        _this.editor.setValue(fData);
      }
      _this.editor.refresh();
    },
    initDbList: function () {
      const _this = this;
      getRequest("/system-config/dbsearch/dbinfoEntity/getDbInfoByUser").then((res) => {
        _this.dbList = res.data.data;
      });
    },
    openDb: function () {
      const _this = this;
      getRequest(`/system-config/dbsearch/dbinfoEntity/openConn/${_this.dbIndex}`).then(
          (res) => {
            _this.tableAndColumnsList = res.data.data;
            let tableList = {};
            if (_this.currentIndex) {
              _this.cacheValue[_this.currentIndex] = _this.editor.getValue();
              let temp = _this.cacheValue[_this.dbIndex];
              _this.editor.setValue(!temp ? "" : temp);
              _this.editor.refresh();
            }
            _this.currentIndex = _this.dbIndex;
            _this.tableAndColumnsList.forEach((item) => {
              tableList[item.tableName] = [];
              item.fields.forEach((sitem) => {
                tableList[item.tableName].push(sitem.filedName);
              });
            });
            _this.editor.setOption("hintOptions", {
              completeSingle: false,
              // 自定义提示选项
              tables: tableList,
            });
          }
      );
    },
  },
};
</script>
